# Import packages
import pandas as pd
import numpy as np
import statsmodels.api as sm
# Set option to display more rows
pd.options.display.max_rows = 30L22: Backtesting -returns
Data
# Load cleaned Compustat data from last lecture
comp = pd.read_pickle('../data/cgs_AG_deciles.zip')
comp.head(2)| permno | mdate | AG_decile | |
|---|---|---|---|
| 0 | 10001 | 1988-06 | 3 |
| 1 | 10001 | 1989-06 | 4 |
# Load clean crsp dataset from last lecture
crsp = pd.read_pickle('../data/crsp_cgs.zip')
crsp.head(2)| permno | mdate | dtdate | ret | MV | mktcap_lag1 | |
|---|---|---|---|---|---|---|
| 0 | 10000 | 1986-02 | 1986-02-28 | -0.257143 | 11.96 | NaN |
| 1 | 10000 | 1986-03 | 1986-03-31 | 0.365385 | 16.33 | 11.96 |
# Merge datasets
mdata = crsp.merge(comp, how='left', on=['permno','mdate'])
mdata| permno | mdate | dtdate | ret | MV | mktcap_lag1 | AG_decile | |
|---|---|---|---|---|---|---|---|
| 0 | 10000 | 1986-02 | 1986-02-28 | -0.257143 | 11.960000 | NaN | NaN |
| 1 | 10000 | 1986-03 | 1986-03-31 | 0.365385 | 16.330000 | 11.960000 | NaN |
| 2 | 10000 | 1986-04 | 1986-04-30 | -0.098592 | 15.172000 | 16.330000 | NaN |
| 3 | 10000 | 1986-05 | 1986-05-30 | -0.222656 | 11.793859 | 15.172000 | NaN |
| 4 | 10000 | 1986-06 | 1986-06-30 | -0.005025 | 11.734594 | 11.793859 | NaN |
| ... | ... | ... | ... | ... | ... | ... | ... |
| 2048100 | 93436 | 2020-08 | 2020-08-31 | 0.741452 | 464339.067705 | 266639.296940 | NaN |
| 2048101 | 93436 | 2020-09 | 2020-09-30 | -0.139087 | 406701.489258 | 464339.067705 | NaN |
| 2048102 | 93436 | 2020-10 | 2020-10-30 | -0.095499 | 367823.512140 | 406701.489258 | NaN |
| 2048103 | 93436 | 2020-11 | 2020-11-30 | 0.462736 | 538028.584458 | 367823.512140 | NaN |
| 2048104 | 93436 | 2020-12 | 2020-12-31 | 0.243252 | 668905.282471 | 538028.584458 | NaN |
2048105 rows × 7 columns
# Look at one of the permnos for a clearer view of the structure of the data
tsla = mdata[mdata['permno']==93436]
tsla.tail(20)| permno | mdate | dtdate | ret | MV | mktcap_lag1 | AG_decile | |
|---|---|---|---|---|---|---|---|
| 2048085 | 93436 | 2019-05 | 2019-05-31 | -0.224266 | 32823.313849 | 41465.465914 | NaN |
| 2048086 | 93436 | 2019-06 | 2019-06-28 | 0.206848 | 40025.709483 | 32823.313849 | 6 |
| 2048087 | 93436 | 2019-07 | 2019-07-31 | 0.081223 | 43278.874579 | 40025.709483 | NaN |
| 2048088 | 93436 | 2019-08 | 2019-08-30 | -0.066222 | 40412.842579 | 43278.874579 | NaN |
| 2048089 | 93436 | 2019-09 | 2019-09-30 | 0.067639 | 43356.599121 | 40412.842579 | NaN |
| 2048090 | 93436 | 2019-10 | 2019-10-31 | 0.307427 | 56762.757820 | 43356.599121 | NaN |
| 2048091 | 93436 | 2019-11 | 2019-11-29 | 0.047695 | 59470.035740 | 56762.757820 | NaN |
| 2048092 | 93436 | 2019-12 | 2019-12-31 | 0.267897 | 75743.664029 | 59470.035740 | NaN |
| 2048093 | 93436 | 2020-01 | 2020-01-31 | 0.555160 | 117793.506666 | 75743.664029 | NaN |
| 2048094 | 93436 | 2020-02 | 2020-02-28 | 0.026776 | 122983.637102 | 117793.506666 | NaN |
| 2048095 | 93436 | 2020-03 | 2020-03-31 | -0.215557 | 96940.000000 | 122983.637102 | NaN |
| 2048096 | 93436 | 2020-04 | 2020-04-30 | 0.492137 | 144937.878385 | 96940.000000 | NaN |
| 2048097 | 93436 | 2020-05 | 2020-05-29 | 0.067939 | 154784.785000 | 144937.878385 | NaN |
| 2048098 | 93436 | 2020-06 | 2020-06-30 | 0.293186 | 200844.670898 | 154784.785000 | 7 |
| 2048099 | 93436 | 2020-07 | 2020-07-31 | 0.325011 | 266639.296940 | 200844.670898 | NaN |
| 2048100 | 93436 | 2020-08 | 2020-08-31 | 0.741452 | 464339.067705 | 266639.296940 | NaN |
| 2048101 | 93436 | 2020-09 | 2020-09-30 | -0.139087 | 406701.489258 | 464339.067705 | NaN |
| 2048102 | 93436 | 2020-10 | 2020-10-30 | -0.095499 | 367823.512140 | 406701.489258 | NaN |
| 2048103 | 93436 | 2020-11 | 2020-11-30 | 0.462736 | 538028.584458 | 367823.512140 | NaN |
| 2048104 | 93436 | 2020-12 | 2020-12-31 | 0.243252 | 668905.282471 | 538028.584458 | NaN |
Create portfolios
# Each month, check back up to 12 months to see what the firm's AG decile was
for t in range(1,13):
# Lag the right way
aux = mdata[['permno','mdate','AG_decile']].copy()
aux['mdate'] = aux['mdate'] + t
aux.rename(columns={'AG_decile':f'dlag{t}'}, inplace=True)
mdata = mdata.merge(aux, how='left', on=['permno','mdate'])
# Check on firm
mdata.loc[mdata['permno']==93436,:]| permno | mdate | dtdate | ret | MV | mktcap_lag1 | AG_decile | dlag1 | dlag2 | dlag3 | dlag4 | dlag5 | dlag6 | dlag7 | dlag8 | dlag9 | dlag10 | dlag11 | dlag12 | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2047979 | 93436 | 2010-07 | 2010-07-30 | -0.163240 | 1858.368170 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2047980 | 93436 | 2010-08 | 2010-08-31 | -0.023069 | 1815.496997 | 1858.368170 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2047981 | 93436 | 2010-09 | 2010-09-30 | 0.047485 | 1902.827529 | 1815.496997 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2047982 | 93436 | 2010-10 | 2010-10-29 | 0.070326 | 2037.038654 | 1902.827529 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2047983 | 93436 | 2010-11 | 2010-11-30 | 0.617674 | 3295.264601 | 2037.038654 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2048100 | 93436 | 2020-08 | 2020-08-31 | 0.741452 | 464339.067705 | 266639.296940 | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2048101 | 93436 | 2020-09 | 2020-09-30 | -0.139087 | 406701.489258 | 464339.067705 | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2048102 | 93436 | 2020-10 | 2020-10-30 | -0.095499 | 367823.512140 | 406701.489258 | NaN | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2048103 | 93436 | 2020-11 | 2020-11-30 | 0.462736 | 538028.584458 | 367823.512140 | NaN | NaN | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN |
| 2048104 | 93436 | 2020-12 | 2020-12-31 | 0.243252 | 668905.282471 | 538028.584458 | NaN | NaN | NaN | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN |
126 rows × 19 columns
# Add up all the lags into a new variable "portf_nr"
mdata['portf_nr'] = mdata.loc[:,mdata.columns.str.contains('dlag')].sum(axis = 1)
# Check one firm
mdata.loc[mdata['permno']==93436,:]| permno | mdate | dtdate | ret | MV | mktcap_lag1 | AG_decile | dlag1 | dlag2 | dlag3 | dlag4 | dlag5 | dlag6 | dlag7 | dlag8 | dlag9 | dlag10 | dlag11 | dlag12 | portf_nr | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 2047979 | 93436 | 2010-07 | 2010-07-30 | -0.163240 | 1858.368170 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 |
| 2047980 | 93436 | 2010-08 | 2010-08-31 | -0.023069 | 1815.496997 | 1858.368170 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 |
| 2047981 | 93436 | 2010-09 | 2010-09-30 | 0.047485 | 1902.827529 | 1815.496997 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 |
| 2047982 | 93436 | 2010-10 | 2010-10-29 | 0.070326 | 2037.038654 | 1902.827529 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 |
| 2047983 | 93436 | 2010-11 | 2010-11-30 | 0.617674 | 3295.264601 | 2037.038654 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 0.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2048100 | 93436 | 2020-08 | 2020-08-31 | 0.741452 | 464339.067705 | 266639.296940 | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 7.0 |
| 2048101 | 93436 | 2020-09 | 2020-09-30 | -0.139087 | 406701.489258 | 464339.067705 | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 7.0 |
| 2048102 | 93436 | 2020-10 | 2020-10-30 | -0.095499 | 367823.512140 | 406701.489258 | NaN | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 7.0 |
| 2048103 | 93436 | 2020-11 | 2020-11-30 | 0.462736 | 538028.584458 | 367823.512140 | NaN | NaN | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | NaN | 7.0 |
| 2048104 | 93436 | 2020-12 | 2020-12-31 | 0.243252 | 668905.282471 | 538028.584458 | NaN | NaN | NaN | NaN | NaN | NaN | 7 | NaN | NaN | NaN | NaN | NaN | NaN | 7.0 |
126 rows × 20 columns
# Drop variables we will not need: all the lags, "timedif", 'AG_decile'
mdata = mdata.loc[:,~mdata.columns.str.contains('dlag')].copy()
# Drop rows where portf_nr = 0 (that means AG was missing for that firm at that date)
mdata = mdata.loc[mdata['portf_nr']!=0, :].copy()
# Save the dataset
mdata.to_pickle('../data/AG_portfolios_panel.zip')
#Check one firm
mdata.loc[mdata['permno']==93436,:]| permno | mdate | dtdate | ret | MV | mktcap_lag1 | AG_decile | portf_nr | |
|---|---|---|---|---|---|---|---|---|
| 2048003 | 93436 | 2012-07 | 2012-07-31 | -0.123682 | 2890.945448 | 3295.556766 | NaN | 10.0 |
| 2048004 | 93436 | 2012-08 | 2012-08-31 | 0.040117 | 3006.920688 | 2890.945448 | NaN | 10.0 |
| 2048005 | 93436 | 2012-09 | 2012-09-28 | 0.026648 | 3097.004233 | 3006.920688 | NaN | 10.0 |
| 2048006 | 93436 | 2012-10 | 2012-10-31 | -0.039228 | 3200.762464 | 3097.004233 | NaN | 10.0 |
| 2048007 | 93436 | 2012-11 | 2012-11-30 | 0.202215 | 3848.005745 | 3200.762464 | NaN | 10.0 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2048100 | 93436 | 2020-08 | 2020-08-31 | 0.741452 | 464339.067705 | 266639.296940 | NaN | 7.0 |
| 2048101 | 93436 | 2020-09 | 2020-09-30 | -0.139087 | 406701.489258 | 464339.067705 | NaN | 7.0 |
| 2048102 | 93436 | 2020-10 | 2020-10-30 | -0.095499 | 367823.512140 | 406701.489258 | NaN | 7.0 |
| 2048103 | 93436 | 2020-11 | 2020-11-30 | 0.462736 | 538028.584458 | 367823.512140 | NaN | 7.0 |
| 2048104 | 93436 | 2020-12 | 2020-12-31 | 0.243252 | 668905.282471 | 538028.584458 | NaN | 7.0 |
102 rows × 8 columns
Calculate portfolio returns
Equal-weighted returns
# Summarize the data for a quick check
mdata.groupby('portf_nr')['ret'].describe()
# the means in this table are NOT average portfolio returns. why?| count | mean | std | min | 25% | 50% | 75% | max | |
|---|---|---|---|---|---|---|---|---|
| portf_nr | ||||||||
| 1.0 | 147074.0 | 0.018182 | 0.309638 | -0.973684 | -0.117514 | -0.006211 | 0.096154 | 24.000000 |
| 2.0 | 153599.0 | 0.017011 | 0.226782 | -0.972173 | -0.085714 | 0.000000 | 0.085714 | 7.480000 |
| 3.0 | 156326.0 | 0.016424 | 0.195320 | -0.965789 | -0.066667 | 0.000000 | 0.076923 | 19.000000 |
| 4.0 | 157548.0 | 0.014418 | 0.163905 | -0.943662 | -0.057143 | 0.003167 | 0.069767 | 7.000000 |
| 5.0 | 158202.0 | 0.014142 | 0.150070 | -0.923077 | -0.053154 | 0.005464 | 0.068209 | 5.500000 |
| 6.0 | 158581.0 | 0.013548 | 0.153137 | -0.928571 | -0.054348 | 0.005400 | 0.068966 | 8.071428 |
| 7.0 | 158833.0 | 0.012584 | 0.161347 | -0.904247 | -0.058824 | 0.004080 | 0.071675 | 14.000000 |
| 8.0 | 158437.0 | 0.012378 | 0.169273 | -0.926702 | -0.066176 | 0.002985 | 0.077250 | 10.344000 |
| 9.0 | 158250.0 | 0.009130 | 0.181519 | -0.981295 | -0.078486 | 0.000000 | 0.082192 | 7.093687 |
| 10.0 | 157495.0 | 0.003622 | 0.216186 | -0.993600 | -0.101009 | -0.004916 | 0.087336 | 13.495050 |
# Equal-weighted portfolio returns each month
ew_ret_monthly = mdata.groupby(['mdate', 'portf_nr'])['ret'].mean()
ew_ret_monthlymdate portf_nr
1982-07 1.0 -0.003948
2.0 -0.011054
3.0 0.002117
4.0 -0.009741
5.0 -0.009275
...
2020-12 6.0 0.071565
7.0 0.084991
8.0 0.113125
9.0 0.092299
10.0 0.090850
Name: ret, Length: 4620, dtype: float64# Reshape to have returns of each portfolio side by side
ew_ret = ew_ret_monthly.unstack(level = 'portf_nr')
ew_ret| portf_nr | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 8.0 | 9.0 | 10.0 |
|---|---|---|---|---|---|---|---|---|---|---|
| mdate | ||||||||||
| 1982-07 | -0.003948 | -0.011054 | 0.002117 | -0.009741 | -0.009275 | -0.003169 | -0.004816 | -0.006250 | -0.034018 | -0.071587 |
| 1982-08 | 0.043106 | 0.058108 | 0.067768 | 0.071293 | 0.070272 | 0.076378 | 0.080404 | 0.078438 | 0.076014 | 0.069051 |
| 1982-09 | 0.037267 | 0.034962 | 0.034323 | 0.037597 | 0.034146 | 0.047353 | 0.034485 | 0.030422 | 0.009593 | -0.020953 |
| 1982-10 | 0.154922 | 0.122708 | 0.106485 | 0.107380 | 0.119641 | 0.122316 | 0.112235 | 0.135088 | 0.142025 | 0.161400 |
| 1982-11 | 0.116354 | 0.115989 | 0.096393 | 0.089470 | 0.069214 | 0.090190 | 0.091515 | 0.082948 | 0.097205 | 0.050597 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2020-08 | -0.017905 | 0.033181 | 0.046718 | 0.054351 | 0.058918 | 0.061198 | 0.049882 | 0.057001 | 0.035579 | 0.086949 |
| 2020-09 | -0.019968 | -0.032700 | -0.013606 | -0.023062 | -0.026950 | -0.032610 | -0.037596 | -0.021940 | -0.034342 | -0.024595 |
| 2020-10 | -0.009192 | 0.000250 | 0.013428 | 0.008144 | 0.004559 | 0.008367 | 0.025419 | 0.014769 | -0.001629 | -0.004516 |
| 2020-11 | 0.277547 | 0.270265 | 0.255649 | 0.216389 | 0.174494 | 0.172263 | 0.170583 | 0.177468 | 0.226676 | 0.218597 |
| 2020-12 | 0.143785 | 0.091213 | 0.119753 | 0.064887 | 0.068347 | 0.071565 | 0.084991 | 0.113125 | 0.092299 | 0.090850 |
462 rows × 10 columns
# Create new column that stores the returns of the "spread" portfolio
ew_ret['Spread'] = ew_ret[1] - ew_ret[10]
ew_ret| portf_nr | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 8.0 | 9.0 | 10.0 | Spread |
|---|---|---|---|---|---|---|---|---|---|---|---|
| mdate | |||||||||||
| 1982-07 | -0.003948 | -0.011054 | 0.002117 | -0.009741 | -0.009275 | -0.003169 | -0.004816 | -0.006250 | -0.034018 | -0.071587 | 0.067639 |
| 1982-08 | 0.043106 | 0.058108 | 0.067768 | 0.071293 | 0.070272 | 0.076378 | 0.080404 | 0.078438 | 0.076014 | 0.069051 | -0.025945 |
| 1982-09 | 0.037267 | 0.034962 | 0.034323 | 0.037597 | 0.034146 | 0.047353 | 0.034485 | 0.030422 | 0.009593 | -0.020953 | 0.058220 |
| 1982-10 | 0.154922 | 0.122708 | 0.106485 | 0.107380 | 0.119641 | 0.122316 | 0.112235 | 0.135088 | 0.142025 | 0.161400 | -0.006478 |
| 1982-11 | 0.116354 | 0.115989 | 0.096393 | 0.089470 | 0.069214 | 0.090190 | 0.091515 | 0.082948 | 0.097205 | 0.050597 | 0.065757 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2020-08 | -0.017905 | 0.033181 | 0.046718 | 0.054351 | 0.058918 | 0.061198 | 0.049882 | 0.057001 | 0.035579 | 0.086949 | -0.104854 |
| 2020-09 | -0.019968 | -0.032700 | -0.013606 | -0.023062 | -0.026950 | -0.032610 | -0.037596 | -0.021940 | -0.034342 | -0.024595 | 0.004627 |
| 2020-10 | -0.009192 | 0.000250 | 0.013428 | 0.008144 | 0.004559 | 0.008367 | 0.025419 | 0.014769 | -0.001629 | -0.004516 | -0.004676 |
| 2020-11 | 0.277547 | 0.270265 | 0.255649 | 0.216389 | 0.174494 | 0.172263 | 0.170583 | 0.177468 | 0.226676 | 0.218597 | 0.058951 |
| 2020-12 | 0.143785 | 0.091213 | 0.119753 | 0.064887 | 0.068347 | 0.071565 | 0.084991 | 0.113125 | 0.092299 | 0.090850 | 0.052935 |
462 rows × 11 columns
# Save the data for later use
ew_ret.to_pickle('../data/AG_ew_returns.zip')Value-weighted returns
# Calculate returns times lagged market cap and sum it up for each portfolio, each month
mdata['ret_x_size'] = mdata['ret'] * mdata['mktcap_lag1']
sum_ret_x_size = mdata.groupby(['mdate','portf_nr'])['ret_x_size'].sum()
sum_ret_x_sizemdate portf_nr
1982-07 1.0 -198.892418
2.0 -1262.453785
3.0 -457.243419
4.0 -2130.685234
5.0 -1447.946100
...
2020-12 6.0 59020.024241
7.0 314564.215682
8.0 140453.373218
9.0 145595.726573
10.0 143887.220882
Name: ret_x_size, Length: 4620, dtype: float64# Calculate sum of lagged market cap for each portfolio each month
sum_size = mdata.groupby(['mdate','portf_nr'])['mktcap_lag1'].sum()
sum_sizemdate portf_nr
1982-07 1.0 1.205728e+04
2.0 3.883712e+04
3.0 5.122119e+04
4.0 1.056480e+05
5.0 1.776978e+05
...
2020-12 6.0 2.856337e+06
7.0 4.875469e+06
8.0 3.383027e+06
9.0 4.879776e+06
10.0 1.983976e+06
Name: mktcap_lag1, Length: 4620, dtype: float64# Calculate monthly VW returns
vw_ret_monthly = sum_ret_x_size / sum_size
vw_ret_monthlymdate portf_nr
1982-07 1.0 -0.016496
2.0 -0.032506
3.0 -0.008927
4.0 -0.020168
5.0 -0.008148
...
2020-12 6.0 0.020663
7.0 0.064520
8.0 0.041517
9.0 0.029837
10.0 0.072525
Length: 4620, dtype: float64# Reshape to have returns of each portfolio side by side
vw_ret = vw_ret_monthly.unstack(level = 'portf_nr')
vw_ret| portf_nr | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 8.0 | 9.0 | 10.0 |
|---|---|---|---|---|---|---|---|---|---|---|
| mdate | ||||||||||
| 1982-07 | -0.016496 | -0.032506 | -0.008927 | -0.020168 | -0.008148 | -0.014216 | -0.016144 | -0.026691 | -0.052065 | -0.063027 |
| 1982-08 | 0.112879 | 0.126619 | 0.133671 | 0.111961 | 0.122147 | 0.108101 | 0.125463 | 0.129065 | 0.111937 | 0.149069 |
| 1982-09 | 0.035342 | -0.011639 | 0.009310 | 0.021978 | 0.013155 | 0.008232 | 0.015515 | 0.021661 | 0.006081 | -0.006955 |
| 1982-10 | 0.180624 | 0.147793 | 0.118260 | 0.096087 | 0.082554 | 0.109715 | 0.099294 | 0.108276 | 0.163611 | 0.183572 |
| 1982-11 | 0.075535 | 0.093501 | 0.050342 | 0.041856 | 0.039193 | 0.049904 | 0.040065 | 0.032300 | 0.074103 | 0.070121 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2020-08 | 0.032559 | 0.139042 | 0.035979 | 0.056023 | 0.058803 | 0.038203 | 0.111807 | 0.092513 | 0.092279 | 0.057890 |
| 2020-09 | 0.004011 | -0.082032 | -0.042604 | -0.012615 | -0.016740 | -0.012480 | -0.048656 | -0.032464 | -0.058615 | -0.023852 |
| 2020-10 | -0.026101 | -0.057324 | -0.011819 | -0.013965 | -0.032251 | -0.025216 | -0.022518 | -0.021996 | -0.025194 | -0.005164 |
| 2020-11 | 0.167945 | 0.119816 | 0.128203 | 0.115096 | 0.120896 | 0.111345 | 0.129932 | 0.119658 | 0.076356 | 0.167235 |
| 2020-12 | 0.053599 | 0.098469 | 0.029864 | 0.019953 | 0.032998 | 0.020663 | 0.064520 | 0.041517 | 0.029837 | 0.072525 |
462 rows × 10 columns
# Create new column that stores the returns of the "spread" portfolio
vw_ret['Spread'] = vw_ret[1] - vw_ret[10]
vw_ret| portf_nr | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 8.0 | 9.0 | 10.0 | Spread |
|---|---|---|---|---|---|---|---|---|---|---|---|
| mdate | |||||||||||
| 1982-07 | -0.016496 | -0.032506 | -0.008927 | -0.020168 | -0.008148 | -0.014216 | -0.016144 | -0.026691 | -0.052065 | -0.063027 | 0.046531 |
| 1982-08 | 0.112879 | 0.126619 | 0.133671 | 0.111961 | 0.122147 | 0.108101 | 0.125463 | 0.129065 | 0.111937 | 0.149069 | -0.036190 |
| 1982-09 | 0.035342 | -0.011639 | 0.009310 | 0.021978 | 0.013155 | 0.008232 | 0.015515 | 0.021661 | 0.006081 | -0.006955 | 0.042297 |
| 1982-10 | 0.180624 | 0.147793 | 0.118260 | 0.096087 | 0.082554 | 0.109715 | 0.099294 | 0.108276 | 0.163611 | 0.183572 | -0.002948 |
| 1982-11 | 0.075535 | 0.093501 | 0.050342 | 0.041856 | 0.039193 | 0.049904 | 0.040065 | 0.032300 | 0.074103 | 0.070121 | 0.005414 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2020-08 | 0.032559 | 0.139042 | 0.035979 | 0.056023 | 0.058803 | 0.038203 | 0.111807 | 0.092513 | 0.092279 | 0.057890 | -0.025331 |
| 2020-09 | 0.004011 | -0.082032 | -0.042604 | -0.012615 | -0.016740 | -0.012480 | -0.048656 | -0.032464 | -0.058615 | -0.023852 | 0.027864 |
| 2020-10 | -0.026101 | -0.057324 | -0.011819 | -0.013965 | -0.032251 | -0.025216 | -0.022518 | -0.021996 | -0.025194 | -0.005164 | -0.020937 |
| 2020-11 | 0.167945 | 0.119816 | 0.128203 | 0.115096 | 0.120896 | 0.111345 | 0.129932 | 0.119658 | 0.076356 | 0.167235 | 0.000710 |
| 2020-12 | 0.053599 | 0.098469 | 0.029864 | 0.019953 | 0.032998 | 0.020663 | 0.064520 | 0.041517 | 0.029837 | 0.072525 | -0.018926 |
462 rows × 11 columns
# Save the data for later use
vw_ret.to_pickle('../data/AG_vw_returns.zip')
vw_ret| portf_nr | 1.0 | 2.0 | 3.0 | 4.0 | 5.0 | 6.0 | 7.0 | 8.0 | 9.0 | 10.0 | Spread |
|---|---|---|---|---|---|---|---|---|---|---|---|
| mdate | |||||||||||
| 1982-07 | -0.016496 | -0.032506 | -0.008927 | -0.020168 | -0.008148 | -0.014216 | -0.016144 | -0.026691 | -0.052065 | -0.063027 | 0.046531 |
| 1982-08 | 0.112879 | 0.126619 | 0.133671 | 0.111961 | 0.122147 | 0.108101 | 0.125463 | 0.129065 | 0.111937 | 0.149069 | -0.036190 |
| 1982-09 | 0.035342 | -0.011639 | 0.009310 | 0.021978 | 0.013155 | 0.008232 | 0.015515 | 0.021661 | 0.006081 | -0.006955 | 0.042297 |
| 1982-10 | 0.180624 | 0.147793 | 0.118260 | 0.096087 | 0.082554 | 0.109715 | 0.099294 | 0.108276 | 0.163611 | 0.183572 | -0.002948 |
| 1982-11 | 0.075535 | 0.093501 | 0.050342 | 0.041856 | 0.039193 | 0.049904 | 0.040065 | 0.032300 | 0.074103 | 0.070121 | 0.005414 |
| ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
| 2020-08 | 0.032559 | 0.139042 | 0.035979 | 0.056023 | 0.058803 | 0.038203 | 0.111807 | 0.092513 | 0.092279 | 0.057890 | -0.025331 |
| 2020-09 | 0.004011 | -0.082032 | -0.042604 | -0.012615 | -0.016740 | -0.012480 | -0.048656 | -0.032464 | -0.058615 | -0.023852 | 0.027864 |
| 2020-10 | -0.026101 | -0.057324 | -0.011819 | -0.013965 | -0.032251 | -0.025216 | -0.022518 | -0.021996 | -0.025194 | -0.005164 | -0.020937 |
| 2020-11 | 0.167945 | 0.119816 | 0.128203 | 0.115096 | 0.120896 | 0.111345 | 0.129932 | 0.119658 | 0.076356 | 0.167235 | 0.000710 |
| 2020-12 | 0.053599 | 0.098469 | 0.029864 | 0.019953 | 0.032998 | 0.020663 | 0.064520 | 0.041517 | 0.029837 | 0.072525 | -0.018926 |
462 rows × 11 columns